MySQL SELECT排序、結果篩選之操作

mysql 自學筆記

no027843 2020-09-30 16:52:37 ‧ 1066 瀏覽

分享至

最近由於工作較忙，故沒什麼時間學習，但還是在百忙之中抽出時間來!

首先，這是我們要操作的資料。

CREATE TABLE IF NOT EXISTS employee(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  title VARCHAR(100) DEFAULT NULL,
  salary DOUBLE DEFAULT NULL,
  hire_date DATE NOT NULL,
  notes TEXT,
  PRIMARY KEY (id)
);

INSERT INTO employee (first_name, last_name, title, salary, hire_date) VALUES 
    ('Robin', 'Jackman', 'Software Engineer', 5500, '2001-10-12'),
    ('Taylor', 'Edward', 'Software Architect', 7200, '2002-09-21'),
    ('Vivian', 'Dickens', 'Database Administrator', 6000, '2012-08-29'),
    ('Harry', 'Clifford', 'Database Administrator', 6800, '2015-12-10'),
    ('Eliza', 'Clifford', 'Software Engineer', 4750, '1998-10-19'),
    ('Nancy', 'Newman', 'Software Engineer', 5100, '2007-01-23'),
    ('Melinda', 'Clifford', 'Project Manager', 8500, '2013-10-29'),
    ('Jack', 'Chan', 'Test Engineer', 6500, '2018-09-07'),
    ('Harley', 'Gilbert', 'Software Architect', 8000, '2000-07-17');

使用 SELECT * FROM table_name order by column; :可將指定資料進行升冪排序，會依照其資料類型進行排序(數字按照大小、字母按照前後順序)

mysql> SELECT * FROM employee order by hire_date;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+

mysql> SELECT * FROM employee order by title;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
9 rows in set (0.00 sec)

SELECT * FROM table_name order by column desc; :而最後面加上desc，可將排序設為降冪

mysql> SELECT * FROM employee order by salary desc;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
9 rows in set (0.00 sec)

SELECT * FROM employee order by 1 desc; 其資料可使用數字替代如1代表column 1。

mysql> SELECT * FROM employee order by 1 desc;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
9 rows in set (0.01 sec)

mysql> SELECT * FROM employee order by 4,3; :當然我們也可以一次進行多個排序，也就是以第4欄排好後，再依照第4欄為基準下去排序第3欄位，可以依照只有排第4欄位及排好第4欄位後再去排第3欄位的差異。

mysql> SELECT * FROM employee order by 4;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
9 rows in set (0.00 sec)

可發現第3欄位的排序也依照字母順序做了變化。

mysql> SELECT * FROM employee order by 4,3;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
9 rows in set (0.00 sec)

利用SELECT * FROM employee order by salary limit 3; : 可以限制返回結果的數量，並且可搭配order,desc等排序方法，來排出如前三高薪水或是前三低薪水等等資料。

mysql> SELECT * FROM employee order by salary limit 3;
+----+------------+-----------+-------------------+--------+------------+-------+
| id | first_name | last_name | title             | salary | hire_date  | notes |
+----+------------+-----------+-------------------+--------+------------+-------+
|  5 | Eliza      | Clifford  | Software Engineer |   4750 | 1998-10-19 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |
+----+------------+-----------+-------------------+--------+------------+-------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM employee order by salary desc limit 3;
+----+------------+-----------+--------------------+--------+------------+-------+
| id | first_name | last_name | title              | salary | hire_date  | notes |
+----+------------+-----------+--------------------+--------+------------+-------+
|  7 | Melinda    | Clifford  | Project Manager    |   8500 | 2013-10-29 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect |   8000 | 2000-07-17 | NULL  |
|  2 | Taylor     | Edward    | Software Architect |   7200 | 2002-09-21 | NULL  |
+----+------------+-----------+--------------------+--------+------------+-------+
3 rows in set (0.00 sec)

SELECT * FROM employee order by salary desc limit 2,3; : 可以返回從第一個參數之值(2)開始，返回第二個參數之值(3)數量的資料。

mysql> SELECT * FROM employee order by salary desc limit 2,3;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
3 rows in set (0.00 sec)

而當我們不知總數量比數，為了確保獲取全部資料，可以設置官方公開之最大資料數量，SELECT * FROM table_name LIMIT 2,18446744073709551615; : 第二個參數設為18446744073709551615可以確保獲取全部的資料。

mysql> SELECT * FROM employee order by salary desc limit 2,18446744073709551615;
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
7 rows in set (0.00 sec)

而當今天在一個情境下，我們忘記資料的全名，但是記得片段字，如我們想找一個C開頭的last_name就可以使用。

SELECT * FROM table_name WHERE column LIKE "C%";

mysql> SELECT *
    -> FROM employee
    -> WHERE last_name
    -> LIKE "C%";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
4 rows in set (0.01 sec)

中間有i的資料

mysql> SELECT *
    -> FROM employee
    -> WHERE last_name
    -> LIKE "%o%";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
3 rows in set (0.00 sec)

如果知道特定長度，可以用_ __an ，即可獲取"Chan"。

mysql> SELECT *
    -> FROM employee
    -> WHERE last_name
    -> LIKE "__an";
+----+------------+-----------+---------------+--------+------------+-------+
| id | first_name | last_name | title         | salary | hire_date  | notes |
+----+------------+-----------+---------------+--------+------------+-------+
|  8 | Jack       | Chan      | Test Engineer |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+---------------+--------+------------+-------+
1 row in set (0.00 sec)

當我們資料中有包含 % , _ 符號時，可以利用\來區分其 % 及 _ ，比如

SELECT * FROM employee WHERE last_name LIKE "%%%";

尋找中間有%且前後都有字的last_name資料。

mysql> SELECT *
    -> FROM employee
    -> WHERE last_name
    -> LIKE "%\%%";
+----+------------+-----------+-------+--------+------------+-------+
| id | first_name | last_name | title | salary | hire_date  | notes |
+----+------------+-----------+-------+--------+------------+-------+
| 10 | ds%        | cc%c      | Tes_t |    777 | 2018-10-10 | NULL  |
+----+------------+-----------+-------+--------+------------+-------+
1 row in set (0.00 sec)

尋找中間有 _ 且前後都有字的last_name資料。

mysql> SELECT *
    -> FROM employee
    -> WHERE title
    -> LIKE "%\_%";
+----+------------+-----------+-------+--------+------------+-------+
| id | first_name | last_name | title | salary | hire_date  | notes |
+----+------------+-----------+-------+--------+------------+-------+
| 10 | ds%        | cc%c      | Tes_t |    777 | 2018-10-10 | NULL  |
+----+------------+-----------+-------+--------+------------+-------+
1 row in set (0.00 sec)

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

902 組

團體組數

37 組

累計文章數

19795 篇

完賽人數

529 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 17th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# linux windows server css react

IT邦幫忙